package com.example.demo

import com.example.demo.bean.Consumer
import com.example.demo.bean.Od
import com.example.demo.bean.DetailOrder
import com.example.demo.bean.Room
import com.mysql.cj.jdbc.Driver
import java.sql.*


object SQLUtil {
    private val connection: Connection?
    private val statement: Statement?
    private val commitConnection: Connection?
    private val pollingThread = object : Thread() {
        override fun run() {
            super.run()
            while (true) {
                sleep(30 * 1000)
                isUser("a", "b")
                cancelOrder(Od("576125312", "576125312", "576125312", "576125312", "576125312", "", 0, 100))
                println("${System.currentTimeMillis()} 轮询 =======")
            }
        }
    }

    init {
        DriverManager.registerDriver(Driver())
        connection = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/Hotel?serverTimezone=UTC&useSSL=false",
            "root",
            "root"
        )
        commitConnection = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/Hotel?serverTimezone=UTC&useSSL=false",
            "root",
            "root"
        )
        commitConnection?.autoCommit = false
        statement = connection?.createStatement()
        //todo 轮询防止与数据库断开连接
        pollingThread.start()
    }

    fun exeSql(sql: String) = statement?.apply {
        this.execute(sql)
    }

//    @JvmOverloads
//    fun exeQuery(sql: String, action: (ResultSet) -> Unit = {}) = statement?.apply {
//        val res = this.executeQuery(sql)
//        action(res)
//    }

    fun exeSafely(sql: String, initParamsAction: (PreparedStatement) -> Any? = {}) = connection?.let {
        val temp = it.prepareStatement(sql)
        initParamsAction(temp)
        temp.execute()
        releaseStatement(temp)
    } ?: false

    private fun releaseStatement(res: Statement) = runCatching {
        res.close()
    }.exceptionOrNull()?.printStackTrace()

    fun release() = runCatching {
        statement?.close()
        connection?.close()
        println("resource release successfully !!!!!!!")
    }.exceptionOrNull()?.printStackTrace()

    fun isUser(name: String, pwd: String): Boolean {
        val sql = "SELECT * FROM user WHERE name = ? AND pwd = ? FOR UPDATE "
        connection?.apply {
            val prepareStatement = prepareStatement(sql)
            prepareStatement.setString(1, name)
            prepareStatement.setString(2, pwd)
            println("$name === $pwd")
            prepareStatement.executeQuery().apply {
                if (next()) {
                    return true
                }
            }
            prepareStatement.close()
        }
        return false
    }

    /**
     * 拿到所有的房间信息
     */
    fun getWholeRoomInfo(): ArrayList<Room> = statement?.let {
        val res = ArrayList<Room>()
        val sql = "SELECT * FROM Room for UPDATE"
        val queries = Factory.createRooms(statement.executeQuery(sql))
        res.addAll(queries)
        res
    } ?: ArrayList()

    /**
     * 拿到特定房间的订单列表
     */
    fun getOrderAboutSpecRoom(roomId: String) = arrayListOf<Od>().apply {
        if (statement == null) return@apply
        val sql = "SELECT * FROM od where roomId = '$roomId'"
        val res = statement.executeQuery(sql)
        val list = arrayListOf<Od>()
        list.addAll(Factory.createOds(res))
    }


    /**
     * 取消订单
     * 1. 修改订单的状态
     * 2.修改room状态
     */
    private fun cancelOrder(order: Od) = commitConnection?.apply {
        val statement = commitConnection.createStatement()
        val updateOrderSql = "UPDATE od SET state = 2 WHERE orderId = '${order.orderId}'"
        val updateRoomSql = "UPDATE room SET state = 0 WHERE roomID = '${order.roomId}'"
        statement.executeUpdate(updateOrderSql)
        statement.executeUpdate(updateRoomSql)
        commit()
    }

    /**
     * 完成订单
     * 1. 修改roomId对应的state
     * 2. 标记订单状态
     */
    private fun completeOrder(od: Od) = commitConnection?.apply {
        val statement = commitConnection.createStatement()
        val updateOrderSql = "UPDATE od SET state = 1 WHERE orderId = '${od.orderId}'"
        val updateRoomSql = "UPDATE room SET state = 0 WHERE roomID = '${od.roomId}'"
        statement.executeUpdate(updateOrderSql)
        statement.executeUpdate(updateRoomSql)
        commit()
    }

    /**
     * 创建订单
     * 1. 修改roomId状态
     * 2. 增加订单
     * 3. 插入consumer的信息 如果之前没有的话
     */
    private fun createOrder(order: Od, consumer: Consumer) = commitConnection?.apply {
        val s1 = commitConnection.createStatement()
        val s2 = commitConnection.createStatement()
        val s3 = commitConnection.createStatement()
        val updateOrderSql =
            "INSERT INTO " +
                    "od (orderId,roomId,consumerId,startTime,endTime,extra,state,allCost) " +
                    "values ('${order.orderId}','${order.roomId}','${order.consumerId}','${order.startTime}','${order.endTime}','${order.extra}',${order.state},${order.allCost})"
        val updateRoomSql = "UPDATE room SET state = 1 WHERE roomID = '${order.roomId}'"
        val sqll = "SELECT * FROM consumer where consumerId = '${consumer.consumerId}'"
        val consumerSql =
            "INSERT INTO consumer(consumerId,name,phoneNumber) VALUES('${consumer.consumerId}','${consumer.name}','${consumer.phoneNumber}')"
        s1.execute(updateOrderSql)
        s2.executeUpdate(updateRoomSql)
        if (!s3.executeQuery(sqll).next())
            s3.execute(consumerSql)
        s1.close()
        s2.close()
        s3.close()
        commit()
    }

    /**
     * 更新订单状态
     * 1.完成订单
     * 2.取消订单
     * 3.新增订单
    {
    "type" : 1,
    “order” : ,
    }
     **/
    fun processOrder(type: Int, order: Od, consumer: Consumer?): Boolean = when (type) {
        1 -> {
            completeOrder(order)
            true
        }
        2 -> {
            cancelOrder(order)
            true
        }
        3 -> {
            createOrder(order, consumer!!)
            true
        }
        else -> false
    }

    fun getTheAllOrderInfo(): List<DetailOrder> = mutableListOf<DetailOrder>().apply {
        if (connection == null) return@apply
        val allOrder = "SELECT * FROM od"
        val allConsumer = "SELECT * FROM consumer"
        val allRoom = "SELECT * FROM room"

        Factory.also {
            val s1 = connection.createStatement()
            val s2 = connection.createStatement()
            val s3 = connection.createStatement()
            val orders = it.createOds(s1.executeQuery(allOrder))
            val consumers = it.createConsumers(s2.executeQuery(allConsumer))
            val rooms = it.createRooms(s3.executeQuery(allRoom))
            orders.forEach { od ->
                add(
                    DetailOrder(
                        od = od,
                        consumer = consumers.firstOrNull { consumer ->
                            consumer.consumerId == od.consumerId
                        },
                        room = rooms.firstOrNull { room ->
                            room.roomId == od.roomId
                        })
                )


            }
            s1.close()
            s2.close()
            s3.close()
        }
    }


}